﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using BCNPCObject.DB;

namespace BCNPCObject
{
    public class cls_NPC_INTOGCS1_Controller
    {
        #region Method

        public DataSet TH_INTOGCS(Int16 i16NamHD, Int16 i16ThangHD, Int16 i16Ky, string strMa_dviqly, string strMa_sogcs, string strMa_khang)
        {
            try
            {
                var qddosogcs = from p in CMIS2.DB.HDG_DDO_SOGCS
                        where p.MA_DVIQLY == strMa_dviqly
                        && p.MA_SOGCS == strMa_sogcs
                        select p;

                var qq = from p in CMIS2.DB.DD_BDONG_HTAI
                         join m in CMIS2.DB.HDG_DDO_SOGCS on new { p.MA_DVIQLY, p.MA_DDO } equals new { m.MA_DVIQLY, m.MA_DDO }
                         where p.MA_DVIQLY == strMa_dviqly
                         && m.MA_DVIQLY == strMa_dviqly
                         && m.MA_SOGCS == strMa_sogcs
                         select new 
                         {
                              p.MA_TBI,
                              m.MA_SOGCS
                         };



                var qddo = from q in qddosogcs.ToList()
                           join p in CMIS2.DB.HDG_DIEM_DO.ToList()
                           on new {q.MA_DVIQLY , q.MA_DDO}
                           equals new {p.MA_DVIQLY, p.MA_DDO}
                           select new
                           {
                               p.MA_DDO,
                               p.MA_KHANG,
                               q.MA_SOGCS,
                               q.STT
                               //p.MA_DVIQLY,
                               //p.MA_DDO,
                               //p.MA_KHANG,
                               //p.DIA_CHI,
                               //p.SO_HO,
                               //m.MA_SOGCS,
                               //m.STT
                           };
                /*
                var qkh = from p in CMIS2.DB.HDG_KHACH_HANG.ToList()
                          join m in qddo.ToList() on new { p.MA_DVIQLY, p.MA_KHANG }
                                                  equals new { m.MA_DVIQLY, m.MA_KHANG }
                          select new 
                          { 
                              m.MA_DVIQLY,
                              m.MA_KHANG,
                              p.TEN_KHANG,
                              m.MA_DDO,
                              m.MA_SOGCS,
                              m.SO_HO,
                              m.STT,
                              m.DIA_CHI
                          };

                var apgia = from p in CMIS2.DB.HDG_BBAN_APGIA.ToList()
                            join m in qkh.ToList() on new { p.MA_DVIQLY, p.MA_DDO }
                                                    equals new { m.MA_DVIQLY, m.MA_DDO }
                            select new 
                            {
                                m.MA_DVIQLY,
                                m.MA_KHANG,
                                m.TEN_KHANG,
                                m.MA_DDO,
                                m.MA_SOGCS,
                                m.SO_HO,
                                m.STT,
                                m.DIA_CHI,
                                p.DINH_MUC,
                                p.LOAI_DMUC,
                                p.MA_NHOMNN
                            };

                var chiso = from p in CMIS2.DB.GCS_CHISO.ToList()
                            join m in apgia.ToList() on new { p.MA_DVIQLY, p.MA_DDO }
                                                     equals new { m.MA_DVIQLY, m.MA_DDO } 
                            where p.THANG == i16ThangHD
                            && p.NAM == i16NamHD
                            && p.KY == i16Ky
                            select new 
                            {
                                m.MA_DVIQLY,
                                m.MA_KHANG,
                                m.TEN_KHANG,
                                m.MA_DDO,
                                m.MA_SOGCS,
                                m.SO_HO,
                                m.STT,
                                m.DIA_CHI,
                                m.DINH_MUC,
                                m.LOAI_DMUC,
                                m.MA_NHOMNN,
                                p.KY,
                                p.THANG,
                                p.NAM,
                                p.CHISO_MOI,
                                p.CHISO_CU,
                                p.LOAI_CHISO
                            };
                var vitriddo = from p in CMIS2.DB.HDG_VITRI_DDO.ToList()
                               join m in chiso.ToList() on new { p.MA_DVIQLY, p.MA_DDO }
                                                        equals new { m.MA_DVIQLY, m.MA_DDO }
                               select new 
                               {
                                   m.MA_DVIQLY,
                                   m.MA_KHANG,
                                   m.TEN_KHANG,
                                   m.MA_DDO,
                                   m.MA_SOGCS,
                                   m.SO_HO,
                                   m.STT,
                                   m.DIA_CHI,
                                   m.DINH_MUC,
                                   m.LOAI_DMUC,
                                   m.MA_NHOMNN,
                                   m.KY,
                                   m.THANG,
                                   m.NAM,
                                   m.CHISO_MOI,
                                   m.CHISO_CU,
                                   m.LOAI_CHISO,
                                   p.MA_TRAM,
                                   p.MA_LO,
                                   p.MA_TO,
                                   p.PHA,
                                   p.SO_COT,
                                   p.SO_HOM
                               };

                var bcs = from p in CMIS2.DB.DD_BCS_CTO.ToList()
                          join m in vitriddo.ToList() on new { p.MA_DVIQLY, p.MA_DDO }
                                                        equals new { m.MA_DVIQLY, m.MA_DDO }
                          select new 
                          {
                              m.MA_DVIQLY,
                              m.MA_KHANG,
                              m.TEN_KHANG,
                              m.MA_DDO,
                              m.MA_SOGCS,
                              m.SO_HO,
                              m.STT,
                              m.DIA_CHI,
                              m.DINH_MUC,
                              m.LOAI_DMUC,
                              m.MA_NHOMNN,
                              m.KY,
                              m.THANG,
                              m.NAM,
                              m.CHISO_MOI,
                              m.CHISO_CU,
                              m.LOAI_CHISO,
                              m.MA_TRAM,
                              m.MA_LO,
                              m.MA_TO,
                              m.PHA,
                              m.SO_COT,
                              m.SO_HOM,
                              p.MA_CTO,
                              p.SO_CTO,
                              p.HS_NHAN
                          };

                var hosocto = from p in CMIS2.DB.DD_HOSO_CTO.ToList()
                              join m in bcs.ToList() on new { p.MA_DVIQLY, p.MA_CTO }
                                                     equals new { m.MA_DVIQLY, m.MA_CTO }
                              select new 
                              {
                                  m.MA_DVIQLY,
                                  m.MA_KHANG,
                                  m.TEN_KHANG,
                                  m.MA_DDO,
                                  m.MA_SOGCS,
                                  m.SO_HO,
                                  m.STT,
                                  m.DIA_CHI,
                                  m.DINH_MUC,
                                  m.LOAI_DMUC,
                                  m.MA_NHOMNN,
                                  m.KY,
                                  m.THANG,
                                  m.NAM,
                                  m.CHISO_MOI,
                                  m.CHISO_CU,
                                  m.LOAI_CHISO,
                                  m.MA_TRAM,
                                  m.MA_LO,
                                  m.MA_TO,
                                  m.PHA,
                                  m.SO_COT,
                                  m.SO_HOM,
                                  m.MA_CTO,
                                  m.SO_CTO,
                                  m.HS_NHAN,
                                  p.MA_CLOAI,
                                  p.LOAI_SOHUU,
                                  p.NAM_SX
                              };

                var tramloto = from p in CMIS2.DB.D_TRAM.ToList()
                               join m in hosocto.ToList() on new { p.MA_DVIQLY, p.MA_TRAM }
                                                            equals new { m.MA_DVIQLY, m.MA_TRAM }
                               from w in CMIS2.DB.D_LO.ToList()
                               join w1 in hosocto.ToList() on new { w.MA_DVIQLY, w.MA_LO }
                                                            equals new { w1.MA_DVIQLY, w1.MA_LO }
                               from e in CMIS2.DB.D_TO.ToList()
                               join e1 in hosocto.ToList() on new { e.MA_DVIQLY, e.MA_TO }
                                                            equals new { e1.MA_DVIQLY, e1.MA_TO }
                               select new 
                               {
                                   m.MA_DVIQLY,
                                   m.MA_KHANG,
                                   m.TEN_KHANG,
                                   m.MA_DDO,
                                   m.MA_SOGCS,
                                   m.SO_HO,
                                   m.STT,
                                   m.DIA_CHI,
                                   m.DINH_MUC,
                                   m.LOAI_DMUC,
                                   m.MA_NHOMNN,
                                   m.KY,
                                   m.THANG,
                                   m.NAM,
                                   m.CHISO_MOI,
                                   m.CHISO_CU,
                                   m.LOAI_CHISO,
                                   m.MA_TRAM,
                                   m.MA_LO,
                                   m.MA_TO,
                                   m.PHA,
                                   m.SO_COT,
                                   m.SO_HOM,
                                   m.MA_CTO,
                                   m.SO_CTO,
                                   m.HS_NHAN,
                                   m.MA_CLOAI,
                                   m.LOAI_SOHUU,
                                   m.NAM_SX,
                                   p.TEN_TRAM,
                                   w.TEN_LO,
                                   e.TEN_TO
                               };

                */

                if (qddo != null && qddo.Count() > 1)
                {
                    DataTable dt = Utility.LINQToDataTable(qddo);
                    dt.TableName = "KQ";
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }
                return null;

                //var apgia = from p in CMIS2.DB.HDG_BBAN_APGIA.ToList()
                //            join m in qkh.ToList() on new {p.MA_DVIQLY, p.MA_DDO}
                //            equals new{m.}

                         
                        
                        


                    //     HDG_KHACH_HANG K1,
                    //HDG_DIEM_DO D1,
                    //HDG_DDO_SOGCS S1,
                    //HDG_BBAN_APGIA AG1,
                    //GCS_CHISO CS1,
                    //HDG_VITRI_DDO DVT,
                    //DD_BCS_CTO CT1,
                    //DD_HOSO_CTO HSCT
            }
            catch
            { return null; }
        }
    
    

        #endregion
    }
}
